# _*_ coding:utf-8 _*_
# @File  : shujukanban_strategy.py
# @Time  : 2022-01-06  10:47
# @Author: zizle
import datetime

import pandas as pd
from fastapi import APIRouter, Query

from db import FAConnection
from status import r_status
from utils.datetime_util import verify_datetime_format
from category import VARIETY_NAME

strategy_api_sjkb = APIRouter()


def groupby_calculate(by, df):
    # 计算总数
    tc_df = df.groupby(by, as_index=False)['creator'].agg({'total_count': 'count'})
    # 计算成功数
    sc_df = df[df['profit'] > 0].groupby(by, as_index=False)['creator'].agg({'success_count': 'count'})
    # 计算总收益
    tp_df = df.groupby(by, as_index=False)['fund'].agg({'total_fund': 'sum'})
    # 计算总资金
    sp_df = df.groupby(by, as_index=False)['profit'].agg({'total_profit': 'sum'})
    # 合并数据
    ret_df = pd.merge(tc_df, sc_df, on=by, how='outer')
    for d in [tp_df, sp_df]:
        ret_df = pd.merge(ret_df, d, on=by, how='outer')
    ret_df.fillna(0, inplace=True)
    # 计算成功率和收益率
    ret_df['s_rate'] = round(ret_df['success_count'] / ret_df['total_count'], 4)
    # 计算收益率
    ret_df['p_rate'] = round(ret_df['total_profit'] / ret_df['total_fund'], 4)
    return ret_df


@strategy_api_sjkb.get('/rate/', summary='查询策略成功率和收益率')
async def strategy_rate(month: int = Query(1)):
    current_day = datetime.datetime.today()
    start_day = current_day + datetime.timedelta(days=-30 * month)
    # 查询数据
    db_conn = FAConnection()
    query_sql = 'SELECT a.id,a.creator,a.create_time,a.variety_en,a.close_time,a.profit,a.fund,' \
                'u.admin_name ' \
                'FROM info_strategy AS a ' \
                'INNER JOIN user_user AS u ON u.id=a.creator ' \
                'WHERE is_delete=0 AND a.close_time IS NOT NULL ' \
                'AND DATE_FORMAT(a.create_time,"%%Y-%%m-%%d")>=%s;'
    records = db_conn.query(query_sql, param=[start_day.strftime('%Y-%m-%d')])
    df = pd.DataFrame(records)
    if df.empty:
        return {'code': r_status.NOT_CONTENT, 'message': '没有数据。', 'user_datalist': [], 'variety_datalist': []}
    df['profit'] = df['profit'].astype(float)
    df['fund'] = df['fund'].astype(float)
    user_df = groupby_calculate(by=['creator', 'admin_name'], df=df)
    user_datalist = user_df.to_dict(orient='records')
    variety_df = groupby_calculate(by=['variety_en'], df=df)
    variety_df['variety_name'] = variety_df['variety_en'].apply(lambda x: VARIETY_NAME.get(x, x))
    variety_datalist = variety_df.to_dict(orient='records')
    return {'code': r_status.SUCCESS, 'message': '查询数据成功。',
            'user_datalist': user_datalist, 'variety_datalist': variety_datalist}


@strategy_api_sjkb.get('/table/', summary='时间段统计成功率和收益率')
async def strategy_table_tj(startdate: str = Query(None), enddate: str = Query(None)):
    startdate = verify_datetime_format(startdate)
    enddate = verify_datetime_format(enddate)
    query_sql = 'SELECT a.id,a.creator,a.close_time,a.variety_en,a.profit,a.fund,' \
                'u.admin_name ' \
                'FROM info_strategy AS a ' \
                'INNER JOIN user_user AS u ON a.creator=u.id ' \
                'WHERE a.close_time IS NOT NULL AND is_delete=0;'
    db_conn = FAConnection()
    df = pd.DataFrame(db_conn.query(query_sql))
    if df.empty:
        return {'code': r_status.NOT_CONTENT, 'message': '没有数据'}
    df['my_date'] = df['close_time'].apply(lambda x: x.strftime('%Y-%m-%d'))
    df['profit'] = df['profit'].astype(float)
    df['fund'] = df['fund'].astype(float)
    # 处理开始和结束时间
    if startdate and not df.empty:
        df = df[df['my_date'] >= startdate]
    if enddate and not df.empty:
        df = df[df['my_date'] <= enddate]
    if df.empty:
        return {'code': r_status.NOT_CONTENT, 'message': '没有数据'}
    # 人员统计
    user_df = groupby_calculate(by=['creator', 'admin_name'], df=df)
    # print(user_df)
    user_datalist = user_df.to_dict(orient='records')
    # 品种统计
    variety_df = groupby_calculate(by='variety_en', df=df)
    variety_df['variety_name'] = variety_df['variety_en'].apply(lambda x: VARIETY_NAME.get(x, x))
    # print(variety_df)
    variety_datalist = variety_df.to_dict(orient='records')
    return {'code': r_status.SUCCESS, 'message': '查询数据成功。',
            'user_datalist': user_datalist, 'variety_datalist': variety_datalist}


# 数据图形
@strategy_api_sjkb.get('/chart/', summary='图形数据接口')
async def exchange_plan_chart(startdate: str = Query(None), enddate: str = Query(None),
                              author: int = Query(None), variety: str = Query(None)):
    startdate = verify_datetime_format(startdate)
    enddate = verify_datetime_format(enddate)
    if not author:
        author = '0'
    query_sql = 'SELECT a.id,a.creator,a.close_time,a.fund,a.profit,a.variety_en ' \
                'FROM info_strategy AS a ' \
                'INNER JOIN user_user AS u ON a.creator=u.id ' \
                'WHERE a.close_time IS NOT NULL AND is_delete=0 ' \
                'AND IF(%s="0",TRUE,creator=%s);'
    db_conn = FAConnection()
    records = db_conn.query(query_sql, param=[author, author])
    df = pd.DataFrame(records)
    if df.empty:
        return {'code': r_status.NOT_CONTENT, 'message': '没有数据!', 'data': []}
    df['my_date'] = df['close_time'].apply(lambda x: x.strftime('%Y-%m-%d'))
    df['profit'] = df['profit'].astype(float)
    df['fund'] = df['fund'].astype(float)
    # 处理开始和结束时间
    if startdate and not df.empty:
        df = df[df['my_date'] >= startdate]
    if enddate and not df.empty:
        df = df[df['my_date'] <= enddate]
    # 处理品种
    if variety:
        df = pd.DataFrame(list(filter(lambda x: x['variety_en'] == variety, df.to_dict(orient='records'))))
    if df.empty:
        return {'code': r_status.NOT_CONTENT, 'message': '没有目标数据!', 'data': []}
    # 计算目标数据(以日期分组计算收益额和收益率)
    group_df = df.groupby('my_date', as_index=False).agg({'profit': 'sum', 'fund': 'sum'})
    # 计算收益率
    group_df['p_rate'] = round(group_df['profit'] / group_df['fund'], 4)
    group_df.sort_values(by='my_date', inplace=True)
    # print(df)
    # print('-' * 50)
    # print(group_df)
    return {'code': r_status.SUCCESS, 'message': '查询数据成功!', 'data': group_df.to_dict(orient='records')}


# 近x条数据成功率和收益率的排名
@strategy_api_sjkb.get('/recently/', summary='近x条的成功率和收益率')
async def exchange_plan_recently(count: int = Query(5)):
    # 查询所有数据
    query_sql = 'SELECT a.id,a.create_time,a.close_time,a.creator,a.profit,a.fund,' \
                'u.admin_name ' \
                'FROM info_strategy AS a ' \
                'INNER JOIN user_user AS u ON a.creator=u.id ' \
                'WHERE a.close_time IS NOT NULL;'
    db_conn = FAConnection()
    df = pd.DataFrame(db_conn.query(query_sql))
    if df.empty:
        return {}
    # 用户分组，取前x条数据
    df['profit'] = df['profit'].astype(float)
    df['fund'] = df['fund'].astype(float)
    df.sort_values(by='create_time', ascending=False, inplace=True)
    ret_df = df.groupby('creator').head(count)
    # 用户分组计算成功率和收益率
    user_df = groupby_calculate(['creator', 'admin_name'], ret_df)
    # 去除掉总数不足count个的
    user_df = user_df[user_df['total_count'] >= count]
    return {'code': r_status.SUCCESS, 'message': '查询数据成功!', 'data': user_df.to_dict(orient='records')}
